//
//  DBManager.swift
//  solarbrain
//
//  Created by MoennigQ on 4/5/18.
//  Copyright © 2018 Client.Solution Pty Ltd. All rights reserved.
//

import UIKit
import FMDB
import HandyJSON

enum JobType: String{
    case pvdJob = "Photovoltaic"
    case serviceJob = "Service Job"
    case swhJob = "Solar Water Heater"
    case all = "Job Type"
}
//0.setting data
let setting_state_table_name = "state"
let setting_suburb_table_name = "suburb"
let setting_streettype_table_name = "streetType"
let setting_unittype_table_name = "unitType"

let setting_field_id = "id"
let setting_field_name = "name"
let setting_field_value = "value"
let setting_suburb_field_postcode = "post_code"
let setting_suburb_field_suburb = "suburb"
let setting_suburb_field_state = "state"

//0-1: Panel installed local info
let pvddetail_table_name = "pvdDetail"
let pvddetail_field_unit_type = "unit_type"
let pvddetail_field_unit_number = "unit_number"
let pvddetail_field_street_number = "street_number"
let pvddetail_field_street_name = "street_name"
let pvddetail_field_street_type = "street_type"
let pvddetail_field_suburb = "suburb"
let pvddetail_field_state = "state"
let pvddetail_field_post_code = "post_code"

//1.checklist master table
let checklist_table_name = "checklists"
let checklist_field_checklistId = "checklist_id"
let checklist_field_type = "type"
let checklist_field_title = "title"
let checklist_field_answer = "answer" //bool or text
let checklist_field_full_name = "full_name"
let checklist_field_answered_at = "answered_at"
//1.1 checklist images table
let checklistImage_table_name = "checklistImages"
let checklistImage_field_imageId = "image_id"
let checklistImage_field_image = "image"
let checklistImage_field_photo_source = "photo_source"//图片来源
let checklistImage_field_photo_created_at = "photo_created_at"//拍照日期
let checklistImage_field_photo_latitude = "latitude"//图片经纬度
let checklistImage_field_photo_longitude = "longitude"
enum ChecklistImageOperType: String{
    case Add = "A", Del = "D"
}
//2.panel number image table
let panelnumber_table_name = "panelNumberImages"
let panelnumber_field_imageId = "image_id"
let panelnumber_field_code = "code"
let panelnumber_field_type = "type"//对应DataOperType的rawValue
let panelnumber_field_image = "image"
let panelnumber_field_auto_scan = "auto_scan"//是否是手动添加：1：扫描的，0：手动添加的
let panelnumber_field_latitude = "latitude"
let panelnumber_field_longitude = "longitude"
let panelnumber_field_barcodetype = "barcode_type"//eg: CODE-128
let panelnumber_field_scan_type = "scan_type"//Dynamsoft or AVFoundation
let panelnumber_field_original_code = "original_code"//首次增加时的code
let panelnumber_field_scan_confidence = "scan_confidence"//扫码可信度
let panelnumber_field_scan_timespan = "scan_timespan"//扫码识别时间
//2.inverter number image table
let inverternumber_table_name = "inverterNumberImages"
enum DataOperType: String{
    case Add = "A", Del = "D", Modify = "M"
}
//3.signatures image table
let signature_table_name = "signatureImages"
let signature_field_type = "signature_type" //Installer,Designer,Owner,Witness
let signature_field_print_name = "print_name"
let signature_field_signed_at = "signed_at"
//two selfie for installer
let signature_field_latitude = "latitude"
let signature_field_longitude = "longitude"
let signature_field_installer_agree_selfie = "installer_agree_selfie"
let signature_field_installer_signature_selfie = "installer_signature_selfie"
let signature_field_installer_design_type = "installer_signature_design_type"
let signature_field_installer_duty_type = "installer_signature_duty_type"
//owner selfie and physicall
let signature_field_owner_signature_selfie = "owner_signature_selfie"
let signature_field_owner_physically_attended = "owner_physically_attended"
let signature_field_owner_aussolarco_satisfactory_version = "owner_aussolarco_satisfactory_version"
let signature_field_owner_aussolarco_completion_version = "owner_satisfaction_completion_version"
let signature_field_owner_financial_incentive = "owner_financial_incentive"
//第一步协议
let signature_field_owner_mandatory_declaration_version = "mandatory_declaration_version"
//
let signature_field_statement_input_param_all = "statement_input_param_all"
//电工表单
let esc_table_name = "electrical_safety_certificate"
let esc_field_checkbox_installation = "checkbox_installation"
let esc_field_checkbox_equipment = "checkbox_equipment"
let esc_field_owner_name_title = "owner_title"
let esc_field_owner_given_name = "owner_given_name"
let esc_field_owner_surname = "owner_surname"
let esc_field_installation_address_street_name = "installation_address_street_name"
let esc_field_installation_address_suburb = "installation_address_suburb"
let esc_field_installation_address_postcode = "installation_address_postcode"
let esc_field_installation_equipment_tested_description = "tested_description"
let esc_field_dateoftest = "date_of_test"
let esc_field_contractor_license_number = "contractor_license_number"
let esc_field_contractor_license_name = "contractor_license_name"
let esc_field_contractor_license_phone = "contractor_license_phone"
let esc_field_electrician_name = "electrician_name"
let esc_field_sign_date = "sign_date"
enum SignatureType: String{
    case Installer = "Installer", Designer = "Designer", Owner = "Owner", Witness = "Witness"
    case InstallerAndDesiger = "Installer&Designer", None = ""
}
//4.service bid job checklist master
let service_checklist_table_name = "serviceChecklists"
//4.1 service bid job checklist images table
let service_checklistImage_table_name = "serviceChecklistImages"
//4.common field
let field_keyId = "id"
let field_pvdId = "pvd_id"
let field_serviceId = "service_id"
let field_imageData = "image_data"
let field_uploadingFlag = "uploading_flag"//正在上传标记，防止重复上传
let field_recordAt = "record_at"
//Job message table
let job_error_history_table_name = "JobErrorHistory"
let job_error_history_id = "server_db_id"//来自server数据库的id，做对比判断
let job_error_history_message_type = "message_type"
let job_error_history_title = "title"
let job_error_history_description = "description"
let job_error_history_module_type = "module_type"
let job_error_history_created_at = "created_at"
let job_error_history_is_read = "is_read"
class DBManager: NSObject {
    static let shared: DBManager = DBManager()
    let databaseFileName = "ezyform.sqlite"
    
    var pathToDatabase: String!
    
    var database: FMDatabase!
    
    var dbQueue: FMDatabaseQueue?
    
    override init() {
        super.init()
        
        let documentsDirectory = (NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true)[0] as NSString) as String
        pathToDatabase = documentsDirectory.appending("/\(databaseFileName)")
        
    }
    //MARK: - 创建数据库
    func createDatabase() {
        
        if !FileManager.default.fileExists(atPath: pathToDatabase) {
            database = FMDatabase(path: pathToDatabase!)
            dbQueue = FMDatabaseQueue(path: pathToDatabase)
            if database != nil {
                // 打开数据库
                if openDatabase() {
                    do {
                        //1.create checklist master table
                        var createTablesQuery: String = "create table if not exists \(checklist_table_name) (\(field_keyId) integer primary key autoincrement not null,\(field_pvdId) text not null, \(checklist_field_checklistId) integer not null,\(checklist_field_type) text not null,\(checklist_field_answer) text ,\(checklist_field_title) text ,\(checklist_field_full_name) text, \(checklist_field_answered_at) text ,  \(field_uploadingFlag) text not null default '0'  ); "
                        try database.executeUpdate(createTablesQuery, values: nil)
                        
                        //1-1.create checklist images table
                        createTablesQuery = "create table if not exists \(checklistImage_table_name) (\(field_keyId) integer primary key autoincrement not null,\(field_pvdId) text not null, \(checklist_field_checklistId) integer not null,\(checklist_field_title) text not null, \(checklistImage_field_imageId) text, \(checklistImage_field_image) text, \(field_recordAt) text ,  \(field_uploadingFlag) text not null default '0'  ); "
                        try database.executeUpdate(createTablesQuery, values: nil)
                        
                        //2.create panel numbers
                        createTablesQuery = "create table if not exists \(panelnumber_table_name) (\(field_keyId) integer primary key autoincrement not null,\(field_pvdId) text not null, \(panelnumber_field_code) text, \(panelnumber_field_imageId) text,\(panelnumber_field_image) text,  \(field_recordAt) text ,  \(field_uploadingFlag) text not null default '0',\(panelnumber_field_type) text not null ); "
                        try database.executeUpdate(createTablesQuery, values: nil)
                        
                        //3.signatuer images table
                        createTablesQuery = "create table if not exists \(signature_table_name) (\(field_keyId) integer primary key autoincrement not null,\(field_pvdId) text not null, \(signature_field_type) text not null, \(signature_field_print_name) text,  \(field_imageData) blob, \(signature_field_signed_at) text ,  \(field_uploadingFlag) text not null default '0'  ); "
                        
                        try database.executeUpdate(createTablesQuery, values: nil)
                    }
                    catch {
                        DebugLog(error.localizedDescription)
                    }
                    // 最后关闭数据库
                    database.close()
                }
                else {
                    DebugLog("Could not open the database.")
                }
            }
        }else{
            dbQueue = FMDatabaseQueue(path: pathToDatabase)
             
//
        }
        self.addNewTables()
        //add new column
        self.alterTables()
        //
        self.resetUploadFlag()
        self.resetServiceUploadFlag()
        
        //将本地JSON基础数据同步到本地数据库
        self.importSettingData()
        //升级服务器上新增的基础数据
        self.upgradeSettingData()
    }
    //MARK: - 这升级suburb，unit_type和street_type, state就那几个数据是写死的
    func upgradeSettingData(){
        //已登录用户
        if APPConfig.loginInfo.access_token != "" , APPConfig.loginInfo.email != ""  {
            //已经做过出事化导入，且有网络
            if let lastUpgradeTime = UserDefaults.standard.object(forKey: "SettingDataUpgradeDatetime") as? String, Connectivity.isConnectedToInternet {
                //suburb
                let vc = DictionaryService()
                vc.getSuburbList(lastUpgradeTime) { list in
                    if self.openDatabase() {
                        for addrModel in list {
                            if let id = addrModel.id {
                                let checkSQL = "select count(*) from \(setting_suburb_table_name) where \(setting_field_id) = \(id)"
                                
                                let sqlStr = "insert into \(setting_suburb_table_name) (\(setting_field_id),\(setting_suburb_field_postcode),\(setting_suburb_field_suburb),\(setting_suburb_field_state)) values(?,?,?,?)"
                                do{
                                    let results: FMResultSet = try self.database.executeQuery(checkSQL, values: nil)
                                    if results.next(){
                                        let cnt: Int32 = results.int(forColumnIndex: 0)
                                        if cnt == 0 {
                                            try self.database.executeUpdate(sqlStr, values: [addrModel.id ?? "",addrModel.code ?? "",addrModel.suburb ?? "",addrModel.state ?? ""])
                                        }else{
                                            let updateSql = "update \(setting_suburb_table_name) set \(setting_suburb_field_postcode) = ?,\(setting_suburb_field_suburb) = ?,\(setting_suburb_field_state) = ? where \(setting_field_id) = ?"
                                            
                                            try self.database.executeUpdate(updateSql, values: [addrModel.code ?? "",addrModel.suburb ?? "",addrModel.state ?? "",id])
                                        }
                                        
                                    }
                                }catch {
                                    DebugLog("Suburb " + self.database.lastErrorMessage())
                                }
                                
                            }
                        }
                        self.database.close()
                    }
                }
                //unit type
                vc.getUnitTypeList(lastUpgradeTime) { list in
                    if self.openDatabase() {
                        for addrModel in list {
                            if let id = addrModel.id {
                                let checkSQL = "select count(*) from \(setting_unittype_table_name) where \(setting_field_id) = \(id)"
                                
                                let sqlStr = "insert into \(setting_unittype_table_name) (\(setting_field_id),\(setting_field_name),\(setting_field_value)) values(?,?,?)"
                                do{
                                    let results: FMResultSet = try self.database.executeQuery(checkSQL, values: nil)
                                    if results.next(){
                                        let cnt: Int32 = results.int(forColumnIndex: 0)
                                        if cnt == 0 {
                                            try self.database.executeUpdate(sqlStr, values: [id,addrModel.name ?? "",addrModel.value ?? ""])
                                        }else{
                                            let updateSql = "update \(setting_unittype_table_name) set \(setting_field_name) = ?,\(setting_field_value) = ? where \(setting_field_id) = ?"
                                            
                                            try self.database.executeUpdate(updateSql, values: [addrModel.name ?? "",addrModel.value ?? "",id])
                                        }
                                        
                                    }
                                }catch {
                                    DebugLog("Unit type" + self.database.lastErrorMessage())
                                }
                                
                            }
                        }
                        self.database.close()
                    }
                }
                //street type
                vc.getStreetTypeList(lastUpgradeTime) { list in
                    if self.openDatabase() {
                        for addrModel in list {
                            if let id = addrModel.id {
                                let checkSQL = "select count(*) from \(setting_streettype_table_name) where \(setting_field_id) = \(id)"
                                
                                let sqlStr = "insert into \(setting_streettype_table_name) (\(setting_field_id),\(setting_field_name),\(setting_field_value)) values(?,?,?)"
                                do {
                                    let results: FMResultSet = try self.database.executeQuery(checkSQL, values: nil)
                                    if results.next(){
                                        let cnt: Int32 = results.int(forColumnIndex: 0)
                                        if cnt == 0 {
                                            try self.database.executeUpdate(sqlStr, values: [id,addrModel.name ?? "",addrModel.value ?? ""])
                                        }else{
                                            let updateSql = "update \(setting_streettype_table_name) set \(setting_field_name) = ?,\(setting_field_value) = ? where \(setting_field_id) = ?"
                                            
                                            try self.database.executeUpdate(updateSql, values: [addrModel.name ?? "",addrModel.value ?? "",id])
                                        }
                                        
                                    }
                                }catch{
                                    DebugLog("Street type" + self.database.lastErrorMessage())
                                }
                                
                            }
                        }
                        self.database.close()
                    }
                }
                let dateStr = DateUtil().getDatetimeStr(Date(), format: "yyyy-MM-dd" )
                UserDefaults.standard.set(dateStr, forKey: "SettingDataUpgradeDatetime")
            }
        }
        
    }
    //MARK: - 将本地数据同步到数据库
    func importSettingData(){
        //初始化导入数据已做过，就不重复此操作
        if let _ = UserDefaults.standard.object(forKey: "SettingDataUpgradeDatetime") as? String {
            return
        }
        if openDatabase() {
            //开启事物批量插入数据只需要0.03秒，如果不开启事物需要12秒（因为一条insert是一个事物）
            database.beginTransaction()
            var isRollBack: Bool = false
            //            print(Date())
            //State list
            var checkSQL = "select count(*) from \(setting_state_table_name) "
            do{
                let results: FMResultSet = try database.executeQuery(checkSQL, values: nil)
                if results.next(){
                    let cnt: Int32 = results.int(forColumnIndex: 0)
                    if cnt == 0 {
                        let sqlStr = "insert into \(setting_state_table_name) (\(setting_field_value),\(setting_field_name)) values(?,?)"
                        for stateName in APPConfig.stateList {
                            try database.executeUpdate(sqlStr, values: [stateName,stateName])
                        }
                    }
                }
            }catch{
                
                DebugLog(database.lastErrorMessage())
                isRollBack = true
                database.rollback()
            }
            //Suburb list
            var path = Bundle.main.path(forResource: "suburb", ofType: "json")
            var url = URL(fileURLWithPath: path!)
            
            checkSQL = "select count(*) from \(setting_suburb_table_name) "
            do{
                let results: FMResultSet = try database.executeQuery(checkSQL, values: nil)
                if results.next(){
                    let cnt: Int32 = results.int(forColumnIndex: 0)
                    if cnt == 0 {
                        
                        let sqlStr = "insert into \(setting_suburb_table_name) (\(setting_field_id),\(setting_suburb_field_postcode),\(setting_suburb_field_suburb),\(setting_suburb_field_state)) values(?,?,?,?)"
                        let data = try Data(contentsOf: url)
                        
                        let jsonData:Any = try JSONSerialization.jsonObject(with: data, options: JSONSerialization.ReadingOptions.mutableContainers)
                        
                        
                        if let jsonArr = jsonData as? [NSDictionary] {
                            for dict in jsonArr {
                                if let id = dict.object(forKey: "id") as? Int, let postCode = dict.object(forKey: "code") as? String, let suburb = dict.object(forKey: "suburb") as? String, let state = dict.object(forKey: "state") as? String{
                                    try database.executeUpdate(sqlStr, values: [id,postCode,suburb,state])
                                }
                                
                            }
                        }
                        
                    }
                }
            }catch{
                DebugLog(database.lastErrorMessage())
                isRollBack = true
                database.rollback()
                
            }
            //street type
            path = Bundle.main.path(forResource: "street_type", ofType: "json")
            url = URL(fileURLWithPath: path!)
            
            checkSQL = "select count(*) from \(setting_streettype_table_name) "
            do{
                let results: FMResultSet = try database.executeQuery(checkSQL, values: nil)
                if results.next(){
                    let cnt: Int32 = results.int(forColumnIndex: 0)
                    if cnt == 0 {
                        let
                        sqlStr = "insert into \(setting_streettype_table_name) (\(setting_field_id),\(setting_field_name),\(setting_field_value)) values(?,?,?)"
                        let data = try Data(contentsOf: url)
                        let jsonData:Any = try JSONSerialization.jsonObject(with: data, options: JSONSerialization.ReadingOptions.mutableContainers)
                        if let jsonArr = jsonData as? [NSDictionary] {
                            for dict in jsonArr {
                                if let id = dict.object(forKey: "id") as? Int, let name = dict.object(forKey: "name") as? String, let value = dict.object(forKey: "value") as? String{
                                    try database.executeUpdate(sqlStr, values: [id,name,value])
                                }
                                
                            }
                        }
                    }
                }
            }catch{
                isRollBack = true
                database.rollback()
                DebugLog(database.lastErrorMessage())
            }
            //unit type
            path = Bundle.main.path(forResource: "unit_type", ofType: "json")
            url = URL(fileURLWithPath: path!)
            
            checkSQL = "select count(*) from \(setting_unittype_table_name) "
            do{
                let results: FMResultSet = try database.executeQuery(checkSQL, values: nil)
                if results.next(){
                    let cnt: Int32 = results.int(forColumnIndex: 0)
                    if cnt == 0 {
                        let
                        sqlStr = "insert into \(setting_unittype_table_name) (\(setting_field_id),\(setting_field_name),\(setting_field_value)) values(?,?,?)"
                        let data = try Data(contentsOf: url)
                        let jsonData:Any = try JSONSerialization.jsonObject(with: data, options: JSONSerialization.ReadingOptions.mutableContainers)
                        if let jsonArr = jsonData as? [NSDictionary] {
                            for dict in jsonArr {
                                if let id = dict.object(forKey: "id") as? Int, let name = dict.object(forKey: "name") as? String, let value = dict.object(forKey: "value") as? String{
                                    try database.executeUpdate(sqlStr, values: [id,name,value])
                                }
                                
                            }
                        }
                    }
                }
            }catch{
                isRollBack = true
                database.rollback()
                DebugLog(database.lastErrorMessage())
            }
            if !isRollBack {
                database.commit()
                
                let dateStr = DateUtil().getDatetimeStr(Date(), format: "yyyy-MM-dd" )
                UserDefaults.standard.set(dateStr, forKey: "SettingDataUpgradeDatetime")
            }
            //            print(Date())
            database.close()
        }
    }
    //MARK: - 打开数据库
    func openDatabase() -> Bool {
        if database == nil {
            if FileManager.default.fileExists(atPath: pathToDatabase) {
                database = FMDatabase(path: pathToDatabase)
            }
        }
        
        if database != nil {
            if database.open() {
                return true
            }
        }
        
        return false
    }
    //MARK: - 修改表结构
    func alterTables(){
        if openDatabase() {
            //是否是手动添加：1：扫描的，0：手动添加的
            if !database.columnExists("\(panelnumber_field_auto_scan)", inTableWithName: "\(panelnumber_table_name)"){
                let sql  = "ALTER TABLE '\(panelnumber_table_name)' ADD '\(panelnumber_field_auto_scan)' integer "
                do{
                    try database.executeUpdate(sql, values: nil)
                }catch{
                    DebugLog(database.lastErrorMessage())
                }
            }
            //维度
            if !database.columnExists("\(panelnumber_field_longitude)", inTableWithName: "\(panelnumber_table_name)"){
                let sql  = "ALTER TABLE '\(panelnumber_table_name)' ADD '\(panelnumber_field_longitude)' decimal "
                do{
                    try database.executeUpdate(sql, values: nil)
                }catch{
                    DebugLog(database.lastErrorMessage())
                }
            }
            //经度
            if !database.columnExists("\(panelnumber_field_latitude)", inTableWithName: "\(panelnumber_table_name)"){
                let sql  = "ALTER TABLE '\(panelnumber_table_name)' ADD '\(panelnumber_field_latitude)' decimal "
                do{
                    try database.executeUpdate(sql, values: nil)
                }catch{
                    DebugLog(database.lastErrorMessage())
                }
            }
            //barcode type
            if !database.columnExists("\(panelnumber_field_barcodetype)", inTableWithName: "\(panelnumber_table_name)"){
                let sql  = "ALTER TABLE '\(panelnumber_table_name)' ADD '\(panelnumber_field_barcodetype)' text "
                do{
                    try database.executeUpdate(sql, values: nil)
                }catch{
                    DebugLog(database.lastErrorMessage())
                }
            }
            //scan type: Dynamsoft or AVFoundation
            if !database.columnExists("\(panelnumber_field_scan_type)", inTableWithName: "\(panelnumber_table_name)"){
                let sql  = "ALTER TABLE '\(panelnumber_table_name)' ADD '\(panelnumber_field_scan_type)' integer "
                do{
                    try database.executeUpdate(sql, values: nil)
                }catch{
                    DebugLog(database.lastErrorMessage())
                }
            }
            //original barcode
            if !database.columnExists("\(panelnumber_field_original_code)", inTableWithName: "\(panelnumber_table_name)"){
                let sql  = "ALTER TABLE '\(panelnumber_table_name)' ADD '\(panelnumber_field_original_code)' text "
                do{
                    try database.executeUpdate(sql, values: nil)
                }catch{
                    DebugLog(database.lastErrorMessage())
                }
            }
            //scan confidence
            if !database.columnExists("\(panelnumber_field_scan_confidence)", inTableWithName: "\(panelnumber_table_name)"){
                let sql  = "ALTER TABLE '\(panelnumber_table_name)' ADD '\(panelnumber_field_scan_confidence)' integer "
                do{
                    try database.executeUpdate(sql, values: nil)
                }catch{
                    DebugLog(database.lastErrorMessage())
                }
            }
            //scan time
            if !database.columnExists("\(panelnumber_field_scan_timespan)", inTableWithName: "\(panelnumber_table_name)"){
                let sql  = "ALTER TABLE '\(panelnumber_table_name)' ADD '\(panelnumber_field_scan_timespan)' integer "
                do{
                    try database.executeUpdate(sql, values: nil)
                }catch{
                    DebugLog(database.lastErrorMessage())
                }
            }
            //*****alert Inverter table
            //original barcode
            if !database.columnExists("\(panelnumber_field_original_code)", inTableWithName: "\(inverternumber_table_name)"){
                let sql  = "ALTER TABLE '\(inverternumber_table_name)' ADD '\(panelnumber_field_original_code)' text "
                do{
                    try database.executeUpdate(sql, values: nil)
                }catch{
                    DebugLog(database.lastErrorMessage())
                }
            }
            //scan confidence
            if !database.columnExists("\(panelnumber_field_scan_confidence)", inTableWithName: "\(inverternumber_table_name)"){
                let sql  = "ALTER TABLE '\(inverternumber_table_name)' ADD '\(panelnumber_field_scan_confidence)' integer "
                do{
                    try database.executeUpdate(sql, values: nil)
                }catch{
                    DebugLog(database.lastErrorMessage())
                }
            }
            //scan time
            if !database.columnExists("\(panelnumber_field_scan_timespan)", inTableWithName: "\(inverternumber_table_name)"){
                let sql  = "ALTER TABLE '\(inverternumber_table_name)' ADD '\(panelnumber_field_scan_timespan)' integer "
                do{
                    try database.executeUpdate(sql, values: nil)
                }catch{
                    DebugLog(database.lastErrorMessage())
                }
            }
            //signature: owner and installer info
            //installer latitude
            if !database.columnExists("\(signature_field_latitude)", inTableWithName: "\(signature_table_name)"){
                let sql  = "ALTER TABLE '\(signature_table_name)' ADD '\(signature_field_latitude)' decimal "
                do{
                    try database.executeUpdate(sql, values: nil)
                }catch{
                    DebugLog(database.lastErrorMessage())
                }
            }
            //installer latitude
            if !database.columnExists("\(signature_field_longitude)", inTableWithName: "\(signature_table_name)"){
                let sql  = "ALTER TABLE '\(signature_table_name)' ADD '\(signature_field_longitude)' decimal "
                do{
                    try database.executeUpdate(sql, values: nil)
                }catch{
                    DebugLog(database.lastErrorMessage())
                }
            }
            //installer agree selfie
            if !database.columnExists("\(signature_field_installer_agree_selfie)", inTableWithName: "\(signature_table_name)"){
                let sql  = "ALTER TABLE '\(signature_table_name)' ADD '\(signature_field_installer_agree_selfie)' text "
                do{
                    try database.executeUpdate(sql, values: nil)
                }catch{
                    DebugLog(database.lastErrorMessage())
                }
            }
            //installer sign selfie
            if !database.columnExists("\(signature_field_installer_signature_selfie)", inTableWithName: "\(signature_table_name)"){
                let sql  = "ALTER TABLE '\(signature_table_name)' ADD '\(signature_field_installer_signature_selfie)' text "
                do{
                    try database.executeUpdate(sql, values: nil)
                }catch{
                    DebugLog(database.lastErrorMessage())
                }
            }
            //installer statement duty type
            if !database.columnExists("\(signature_field_installer_duty_type)", inTableWithName: "\(signature_table_name)"){
                let sql  = "ALTER TABLE '\(signature_table_name)' ADD '\(signature_field_installer_duty_type)' text "
                do{
                    try database.executeUpdate(sql, values: nil)
                }catch{
                    DebugLog(database.lastErrorMessage())
                }
            }
            //installer statement design type
            if !database.columnExists("\(signature_field_installer_design_type)", inTableWithName: "\(signature_table_name)"){
                let sql  = "ALTER TABLE '\(signature_table_name)' ADD '\(signature_field_installer_design_type)' text "
                do{
                    try database.executeUpdate(sql, values: nil)
                }catch{
                    DebugLog(database.lastErrorMessage())
                }
            }
            //owner sign selfie
            if !database.columnExists("\(signature_field_owner_signature_selfie)", inTableWithName: "\(signature_table_name)"){
                let sql  = "ALTER TABLE '\(signature_table_name)' ADD '\(signature_field_owner_signature_selfie)' text "
                do{
                    try database.executeUpdate(sql, values: nil)
                }catch{
                    DebugLog(database.lastErrorMessage())
                }
            }
            //owner physically_attended
            if !database.columnExists("\(signature_field_owner_physically_attended)", inTableWithName: "\(signature_table_name)"){
                let sql  = "ALTER TABLE '\(signature_table_name)' ADD '\(signature_field_owner_physically_attended)' text "
                do{
                    try database.executeUpdate(sql, values: nil)
                }catch{
                    DebugLog(database.lastErrorMessage())
                }
            }
            //signature_field_owner_aussolarco_satisfactory_version
            if !database.columnExists("\(signature_field_owner_aussolarco_satisfactory_version)", inTableWithName: "\(signature_table_name)"){
                let sql  = "ALTER TABLE '\(signature_table_name)' ADD '\(signature_field_owner_aussolarco_satisfactory_version)' text "
                do{
                    try database.executeUpdate(sql, values: nil)
                }catch{
                    DebugLog(database.lastErrorMessage())
                }
            }
            //signature_field_owner_aussolarco_completion_version
            if !database.columnExists("\(signature_field_owner_aussolarco_completion_version)", inTableWithName: "\(signature_table_name)"){
                let sql  = "ALTER TABLE '\(signature_table_name)' ADD '\(signature_field_owner_aussolarco_completion_version)' text "
                do{
                    try database.executeUpdate(sql, values: nil)
                }catch{
                    DebugLog(database.lastErrorMessage())
                }
            }
            //signature_field_owner_mandatory_declaration_version
            if !database.columnExists("\(signature_field_owner_mandatory_declaration_version)", inTableWithName: "\(signature_table_name)"){
                let sql  = "ALTER TABLE '\(signature_table_name)' ADD '\(signature_field_owner_mandatory_declaration_version)' text "
                do{
                    try database.executeUpdate(sql, values: nil)
                }catch{
                    DebugLog(database.lastErrorMessage())
                }
            }
            //signature_field_owner_financial_incentive
            if !database.columnExists("\(signature_field_owner_financial_incentive)", inTableWithName: "\(signature_table_name)"){
                let sql  = "ALTER TABLE '\(signature_table_name)' ADD '\(signature_field_owner_financial_incentive)' text "
                do{
                    try database.executeUpdate(sql, values: nil)
                }catch{
                    DebugLog(database.lastErrorMessage())
                }
            }
            //signature_field_statement_input_param_all
            if !database.columnExists("\(signature_field_statement_input_param_all)", inTableWithName: "\(signature_table_name)"){
                let sql  = "ALTER TABLE '\(signature_table_name)' ADD '\(signature_field_statement_input_param_all)' text "
                do{
                    try database.executeUpdate(sql, values: nil)
                }catch{
                    DebugLog(database.lastErrorMessage())
                }
            }
            //photo source: 1代表来源于相册，2代表来源于拍照
            if !database.columnExists("\(checklistImage_field_photo_source)", inTableWithName: "\(checklistImage_table_name)"){
                let sql  = "ALTER TABLE '\(checklistImage_table_name)' ADD '\(checklistImage_field_photo_source)' text "
                do{
                    try database.executeUpdate(sql, values: nil)
                }catch{
                    DebugLog(database.lastErrorMessage())
                }
            }
            //photo create at
            if !database.columnExists("\(checklistImage_field_photo_created_at)", inTableWithName: "\(checklistImage_table_name)"){
                let sql  = "ALTER TABLE '\(checklistImage_table_name)' ADD '\(checklistImage_field_photo_created_at)' text "
                do{
                    try database.executeUpdate(sql, values: nil)
                }catch{
                    DebugLog(database.lastErrorMessage())
                }
            }
            //longitude
            if !database.columnExists("\(checklistImage_field_photo_longitude)", inTableWithName: "\(checklistImage_table_name)"){
                let sql  = "ALTER TABLE '\(checklistImage_table_name)' ADD '\(checklistImage_field_photo_longitude)' text "
                do{
                    try database.executeUpdate(sql, values: nil)
                }catch{
                    DebugLog(database.lastErrorMessage())
                }
            }
            //latitude
            if !database.columnExists("\(checklistImage_field_photo_latitude)", inTableWithName: "\(checklistImage_table_name)"){
                let sql  = "ALTER TABLE '\(checklistImage_table_name)' ADD '\(checklistImage_field_photo_latitude)' text "
                do{
                    try database.executeUpdate(sql, values: nil)
                }catch{
                    DebugLog(database.lastErrorMessage())
                }
            }
            database.close()
        }
    }
    //MARK: - 增加新表
    func addNewTables(){
        if openDatabase() {
            
            do{
                //1.service bid job checklist master table
                var createTablesQuery = "create table if not exists \(service_checklist_table_name) (\(field_keyId) integer primary key autoincrement not null,\(field_serviceId) text not null, \(checklist_field_checklistId) integer not null,\(checklist_field_type) text not null,\(checklist_field_answer) text ,\(checklist_field_title) text ,\(checklist_field_full_name) text, \(checklist_field_answered_at) text ,  \(field_uploadingFlag) text not null default '0'  ); "
                try database.executeUpdate(createTablesQuery, values: nil)
                //1-1.service bid job checklist images table
                createTablesQuery = "create table if not exists \(service_checklistImage_table_name) (\(field_keyId) integer primary key autoincrement not null,\(field_serviceId) text not null, \(checklist_field_checklistId) integer not null,\(checklist_field_title) text not null, \(checklistImage_field_imageId) text, \(checklistImage_field_image) text, \(field_recordAt) text ,  \(field_uploadingFlag) text not null default '0'  ); "
                try database.executeUpdate(createTablesQuery, values: nil)
                //2.create inverter numbers
                createTablesQuery = "create table if not exists \(inverternumber_table_name) (\(field_keyId) integer primary key autoincrement not null,\(field_pvdId) text not null, \(panelnumber_field_code) text, \(panelnumber_field_imageId) text,\(panelnumber_field_image) text,  \(field_recordAt) text ,  \(field_uploadingFlag) text not null default '0',\(panelnumber_field_type) text not null, \(panelnumber_field_auto_scan) integer, \(panelnumber_field_latitude) decimal, \(panelnumber_field_longitude) decimal, \(panelnumber_field_barcodetype) text, \(panelnumber_field_scan_type) integer ); "
                try database.executeUpdate(createTablesQuery, values: nil)
                //4.Setting data: State List
                createTablesQuery = "create table if not exists \(setting_state_table_name) (\(setting_field_id) integer not null primary key autoincrement not null,\(setting_field_name) text not null, \(setting_field_value) text not null);"
                try database.executeUpdate(createTablesQuery, values: nil)
                //5.Setting data: Suburb List
                createTablesQuery = "create table if not exists \(setting_suburb_table_name) (\(setting_field_id) integer primary key not null,\(setting_suburb_field_postcode) text not null, \(setting_suburb_field_suburb) text not null, \(setting_suburb_field_state) text not null);"
                try database.executeUpdate(createTablesQuery, values: nil)
                //6.Setting data: Street type List
                createTablesQuery = "create table if not exists \(setting_streettype_table_name) (\(setting_field_id) integer primary key not null,\(setting_field_name) text not null, \(setting_field_value) text not null);"
                try database.executeUpdate(createTablesQuery, values: nil)
                //7.Setting data: Unit type List
                createTablesQuery = "create table if not exists \(setting_unittype_table_name) (\(setting_field_id) integer primary key not null,\(setting_field_name) text not null, \(setting_field_value) text not null);"
                try database.executeUpdate(createTablesQuery, values: nil)
                //8.ESC table
                createTablesQuery = "create table if not exists \(esc_table_name) (\(field_keyId) integer primary key autoincrement not null,\(field_pvdId) text not null,\(esc_field_checkbox_installation) bool , \(esc_field_checkbox_equipment) bool , \(esc_field_owner_name_title) text, \(esc_field_owner_given_name) text, \(esc_field_owner_surname) text, \(esc_field_installation_address_street_name) text, \(esc_field_installation_address_suburb) text, \(esc_field_installation_address_postcode) text, \(esc_field_installation_equipment_tested_description) text, \(esc_field_dateoftest) text, \(esc_field_contractor_license_number) text, \(esc_field_contractor_license_name) text, \(esc_field_contractor_license_phone) text, \(esc_field_electrician_name) text, \(esc_field_sign_date) text);"

                try database.executeUpdate(createTablesQuery, values: nil)
                //8.Job Error histories table
                createTablesQuery = "create table if not exists \(job_error_history_table_name) (\(field_keyId) integer primary key autoincrement not null,\(job_error_history_id) integer not null,\(field_pvdId) text not null,\(job_error_history_message_type) text , \(job_error_history_module_type) text , \(job_error_history_title) text, \(job_error_history_description) text, \(job_error_history_is_read) bool, \(job_error_history_created_at) text);"

                try database.executeUpdate(createTablesQuery, values: nil)
            }catch {
                DebugLog(error.localizedDescription)
            }
            database.close()
        }
    }
    //MARK: - get status list
    func getStateList()->[AddressModel]{
        var list = [AddressModel]()
        if openDatabase() {
            do {
                let query = "select * from \(setting_state_table_name) "
                
                let results = try database.executeQuery(query, values: nil)
                
                while results.next() {
                    let model = AddressModel()
                    //value和name是一样的
                    model.value = results.string(forColumn: "\(setting_field_value)")
                    model.name = results.string(forColumn: "\(setting_field_name)")
                    list.append(model)
                }
            }catch {
                DebugLog(error.localizedDescription)
            }
            database.close()
        }
        return list
    }
    //MARK: - get suburb list
    func getSuburbListByPostcode(_ postcode: String)->[PhysicalAddressViewModel]{
        var list = [PhysicalAddressViewModel]()
        if openDatabase() {
            do {
                let query = "select * from \(setting_suburb_table_name) where \(setting_suburb_field_postcode) like ? order by \(setting_field_id) asc"
                
                let results = try database.executeQuery(query, values: ["%\(postcode)%"])
                
                while results.next() {
                    let model = PhysicalAddressViewModel()
                    //value和name是一样的
                    model.post_code = results.string(forColumn: "\(setting_suburb_field_postcode)") ?? ""
                    model.suburb = results.string(forColumn: "\(setting_suburb_field_suburb)") ?? ""
                    model.state = results.string(forColumn: "\(setting_suburb_field_state)") ?? ""
                    list.append(model)
                }
            }catch {
                DebugLog(error.localizedDescription)
            }
            database.close()
        }
        return list
    }
    //MARK: - 根据unit type value获取unit type name
    func getUnitTypeNameByValue(_ value: String)->String{
        var unitTypeName = value
        if openDatabase() {
            do {
                
                let query = "select * from \(setting_unittype_table_name) where \(setting_field_value) = ?"
                
                let results = try database.executeQuery(query, values: [value])
                
                while results.next() {
                    unitTypeName = results.string(forColumn: "\(setting_field_name)") ?? ""
                }
            }catch {
                DebugLog(error.localizedDescription)
            }
            database.close()
        }
        return unitTypeName
    }
    //MARK: - 根据street type value获取street type name
    func getStreetTypeNameByValue(_ value: String)->String{
        var streetTypeName = value
        if openDatabase() {
            do {
                
                let query = "select * from \(setting_streettype_table_name) where \(setting_field_value) = ?"
                
                let results = try database.executeQuery(query, values: [value])
                
                while results.next() {
                    streetTypeName = results.string(forColumn: "\(setting_field_name)") ?? ""
                }
            }catch {
                DebugLog(error.localizedDescription)
            }
            database.close()
        }
        return streetTypeName
    }
    //MARK: - get street type list
    func getStreetTypeList(_ name: String)->[PhysicalAddressViewModel]{
        var list = [PhysicalAddressViewModel]()
        if openDatabase() {
            do {
                
                let query = "select * from \(setting_streettype_table_name) where \(setting_field_name) like ? or \(setting_field_value) like ?"
                
                let results = try database.executeQuery(query, values: ["%\(name)%","%\(name)%"])
                
                while results.next() {
                    let model = PhysicalAddressViewModel()
                    model.street_type = results.string(forColumn: "\(setting_field_value)") ?? ""
                    model.street_type_name = results.string(forColumn: "\(setting_field_name)") ?? ""
                    list.append(model)
                }
            }catch {
                DebugLog(error.localizedDescription)
            }
            database.close()
        }
        return list
    }
    //MARK: - get unit type list
    func getUnitTypeList(_ name: String)->[PhysicalAddressViewModel]{
        var list = [PhysicalAddressViewModel]()
        if openDatabase() {
            do {
                let query = "select * from \(setting_unittype_table_name) where \(setting_field_name) like ? or \(setting_field_value) like ?"
                
                let results = try database.executeQuery(query, values: ["%\(name)%","%\(name)%"])
                
                while results.next() {
                    let model = PhysicalAddressViewModel()
                    
                    model.unit_type = results.string(forColumn: "\(setting_field_value)") ?? ""
                    model.unit_type_name = results.string(forColumn: "\(setting_field_name)") ?? ""
                    list.append(model)
                }
            }catch {
                DebugLog(error.localizedDescription)
            }
            database.close()
        }
        return list
    }
    
    //MARK: - get electrical safety certificate infomation
    func getESCInfo()->ESCInfoModel{
        let model = ESCInfoModel()
        if openDatabase() {
            do {
                let query = "select * from \(esc_table_name) where \(field_pvdId) = \(JobDetailModelHelper.shared.jobDetailModel.pvd_id)"
                
                let results = try database.executeQuery(query, values: nil)
                
                while results.next() {
                    model.installation_flag = results.bool(forColumn: "\(esc_field_checkbox_installation)")
                    model.equipment_flag = results.bool(forColumn: "\(esc_field_checkbox_equipment)")
                    model.owner_name_title = results.string(forColumn: "\(esc_field_owner_name_title)")
                    model.owner_given_name = results.string(forColumn: "\(esc_field_owner_given_name)")
                    model.owner_surname = results.string(forColumn: "\(esc_field_owner_surname)")
                    model.installation_address_street_name = results.string(forColumn: "\(esc_field_installation_address_street_name)")
                    model.installation_address_suburb = results.string(forColumn: "\(esc_field_installation_address_suburb)")
                    model.installation_address_postcode = results.string(forColumn: "\(esc_field_installation_address_postcode)")
                    model.installation_equipment_tested_descrip = results.string(forColumn: "\(esc_field_installation_equipment_tested_description)")
                    model.date_of_test = results.string(forColumn: "\(esc_field_dateoftest)")
                    model.contractor_licence_number = results.string(forColumn: "\(esc_field_contractor_license_number)")
                    model.contractor_licence_name = results.string(forColumn: "\(esc_field_contractor_license_name)")
                    model.contractor_licence_phone = results.string(forColumn: "\(esc_field_contractor_license_phone)")
                    model.electrician_name = results.string(forColumn: "\(esc_field_electrician_name)")
                    model.sign_date = results.string(forColumn: "\(esc_field_sign_date)")
                }
            }catch {
                DebugLog(error.localizedDescription)
            }
            database.close()
        }
        return model
    }
    //MARK: - 保存esc信息
    func saveESCInfo(_ model: ESCInfoModel){
        if openDatabase() {
            do{
                let checkSQL = "select count(*) from \(esc_table_name) where \(field_pvdId) = \(JobDetailModelHelper.shared.jobDetailModel.pvd_id)"
                
                let results: FMResultSet = try database.executeQuery(checkSQL, values: nil)
                if results.next(){
                    let cnt: Int32 = results.int(forColumnIndex: 0)
                    if cnt == 0 {
                        let sqlStr = "insert into \(esc_table_name) (\(field_pvdId),\(esc_field_checkbox_installation),\(esc_field_checkbox_equipment),\(esc_field_owner_name_title),\(esc_field_owner_given_name),\(esc_field_owner_surname),\(esc_field_installation_address_street_name),\(esc_field_installation_address_suburb),\(esc_field_installation_address_postcode),\(esc_field_installation_equipment_tested_description),\(esc_field_dateoftest),\(esc_field_contractor_license_number),\(esc_field_contractor_license_name),\(esc_field_contractor_license_phone),\(esc_field_electrician_name),\(esc_field_sign_date))  values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
                        let values1: [Any] = [JobDetailModelHelper.shared.jobDetailModel.pvd_id,
                                        model.installation_flag ?? false,
                                        model.equipment_flag ?? false,
                                        model.owner_name_title ?? "",
                                        model.owner_given_name ?? "",
                                        model.owner_surname ?? "",
                                        model.installation_address_street_name ?? "",
                                        model.installation_address_suburb ?? "",
                                        model.installation_address_postcode ?? ""] as [Any]
                        
                        let values2: [Any] = [model.installation_equipment_tested_descrip ?? "",
                                     model.date_of_test ?? "",
                                     model.contractor_licence_number ?? "",
                                     model.contractor_licence_name ?? "",
                                     model.contractor_licence_phone ?? "",
                                     model.electrician_name ?? "",
                                     model.sign_date ?? ""] as [Any]
                        let values = values1 + values2
                        try database.executeUpdate(sqlStr, values: values )
                    }else{
                        let updateSql = "update \(esc_table_name)" +
                            " set \(esc_field_checkbox_installation) = ?," +
                            "\(esc_field_checkbox_equipment) = ?," +
                            "\(esc_field_owner_name_title) = ?," +
                            "\(esc_field_owner_given_name) = ?," +
                            "\(esc_field_owner_surname) = ?," +
                            "\(esc_field_installation_address_street_name) = ?," +
                            "\(esc_field_installation_address_suburb) = ?," +
                            "\(esc_field_installation_address_postcode) = ?," +
                            "\(esc_field_installation_equipment_tested_description) = ?," +
                            "\(esc_field_dateoftest) = ?," +
                            "\(esc_field_contractor_license_number) = ?," +
                            "\(esc_field_contractor_license_name) = ?," +
                            "\(esc_field_contractor_license_phone) = ?," +
                            "\(esc_field_electrician_name) = ?," +
                            "\(esc_field_sign_date) = ?" +
                            " where \(field_pvdId) = \(JobDetailModelHelper.shared.jobDetailModel.pvd_id)"
                        let values1: [Any] = [model.installation_flag ?? false,
                                        model.equipment_flag ?? false,
                                        model.owner_name_title ?? "",
                                        model.owner_given_name ?? "",
                                        model.owner_surname ?? "",
                                        model.installation_address_street_name ?? "",
                                        model.installation_address_suburb ?? "",
                                        model.installation_address_postcode ?? ""] as [Any]
                        
                        let values2: [Any] = [model.installation_equipment_tested_descrip ?? "",
                                     model.date_of_test ?? "",
                                     model.contractor_licence_number ?? "",
                                     model.contractor_licence_name ?? "",
                                     model.contractor_licence_phone ?? "",
                                     model.electrician_name ?? "",
                                     model.sign_date ?? ""] as [Any]
                        let values = values1 + values2
                        try database.executeUpdate(updateSql, values: values)
                    }
                    
                }
                
            }catch {
                DebugLog(database.lastErrorMessage())
            }
            
            database.close()
        }
    }
    //MARK: 增加checklist本地数据（bool和text）
    func insertChecklistData(model: PVDChecklistModel) {
        if openDatabase() {
            
            do{
                let checkSQL = "select count(*) from \(checklist_table_name) where \(field_pvdId) = \(model.pvdId) and \(checklist_field_checklistId) = \(model.id)"
                
                let sqlStr = "insert into \(checklist_table_name) (\(field_pvdId),\(checklist_field_checklistId),\(checklist_field_type),\(checklist_field_title),\(checklist_field_answer),\(checklist_field_answered_at),\(checklist_field_full_name)) values(?,?,?,?,?,?,?)"
                
                let results: FMResultSet = try database.executeQuery(checkSQL, values: nil)
                if results.next(){
                    let cnt: Int32 = results.int(forColumnIndex: 0)
                    if cnt == 0 {
                        try database.executeUpdate(sqlStr, values: [model.pvdId,model.id,model.type, model.title,model.answer,model.answered_at,model.full_name])
                    }else{
                        let updateSql = "update \(checklist_table_name) set \(checklist_field_answer) = ?,\(checklist_field_answered_at) = ?,\(checklist_field_title) = ?,\(checklist_field_full_name) = ? where \(field_pvdId) = ? and \(checklist_field_checklistId) = ?"
                        
                        try database.executeUpdate(updateSql, values: [model.answer,model.answered_at ,model.title,model.full_name ,model.pvdId,model.id])
                    }
                    
                }
                
            }catch {
                DebugLog(database.lastErrorMessage())
            }
            
            database.close()
        }
    }
    //MARK: - 获取image的DB id
    func getChecklistImageByRecordAt(_ imageModel: CheckListImageDBModel, jobType: JobType = .pvdJob)->Int32?{
        var dbID: Int32?
        
        if openDatabase() {
            do {
                var query = ""
                if jobType == .pvdJob{//pvd
                    query = "select \(field_keyId) from \(checklistImage_table_name) where \(field_pvdId) = ? and \(checklist_field_checklistId) = ? and \(field_recordAt) = ? "
                }else{//service
                    query = "select \(field_keyId) from \(service_checklistImage_table_name) where \(field_serviceId) = ? and \(checklist_field_checklistId) = ? and \(field_recordAt) = ? "
                }
                let results = try database.executeQuery(query, values: [imageModel.pvd_id,imageModel.checklist_id, imageModel.record_at ])
                
                while results.next() {
                    dbID = results.int(forColumn: "\(field_keyId)")
                }
            }
            catch {
                DebugLog(database.lastErrorMessage())
            }
            database.close()
        }
        
        return dbID
    }
    //MARK: - 检查image是否正在上传中
    func checkIfImageUploadingByRecordAt(_ imageModel: CheckListImageDBModel, jobType: JobType = .pvdJob)->Bool{
        var uploadFlag: Bool = false
        
        if openDatabase() {
            do {
                var query = ""
                if jobType == .pvdJob{//pvd
                    query = "select \(field_keyId) from \(checklistImage_table_name) where \(field_pvdId) = ? and \(checklist_field_checklistId) = ? and \(field_recordAt) = ? and \(field_uploadingFlag) = '1'"
                }else{//service
                    query = "select \(field_keyId) from \(service_checklistImage_table_name) where \(field_serviceId) = ? and \(checklist_field_checklistId) = ? and \(field_recordAt) = ? and \(field_uploadingFlag) = '1'"
                }
                let results = try database.executeQuery(query, values: [imageModel.pvd_id,imageModel.checklist_id, imageModel.record_at])
                
                while results.next() {
                    uploadFlag = true
                }
            }
            catch {
                DebugLog(database.lastErrorMessage())
            }
            database.close()
        }
        
        return uploadFlag
    }
    //MARK: - 增加image数据
    func insertChecklistImages(model: CheckListImageDBModel) {
        if openDatabase() {
            do{
                //2.add image data
                let imageStr = "insert into \(checklistImage_table_name) (\(field_pvdId),\(checklist_field_checklistId),\(checklistImage_field_imageId),\(checklistImage_field_image),\(field_recordAt),\(field_uploadingFlag),\(checklist_field_title),\(checklistImage_field_photo_source),\(checklistImage_field_photo_created_at),\(checklistImage_field_photo_longitude),\(checklistImage_field_photo_latitude)) values(?,?,?,?,?,?,?,?,?,?,?)"
                
                
                try database.executeUpdate(imageStr, values: [model.pvd_id ,model.checklist_id ,model.id,model.image,model.record_at ,"0",model.title ,model.photo_source ,model.photo_created_at ,model.longitude ,model.latitude])
                
                //1.check if checklist data exist in master table
                let checkSQL = "select count(*) from \(checklist_table_name) where \(field_pvdId) = \(model.pvd_id) and \(checklist_field_checklistId) = \(model.checklist_id)"
                
                let results: FMResultSet = try database.executeQuery(checkSQL, values: nil)
                if results.next(){
                    let cnt: Int32 = results.int(forColumnIndex: 0)
                    if cnt == 0 {
                        let addMasterSql = "insert into \(checklist_table_name) (\(field_pvdId),\(checklist_field_checklistId),\(checklist_field_type),\(checklist_field_title),\(checklist_field_full_name),\(checklist_field_answered_at)) values(?,?,?,?,?,?)"
                        
                        try database.executeUpdate(addMasterSql, values: [model.pvd_id,model.checklist_id,"image",model.title ,(APPConfig.loginInfo.fullName),model.record_at])
                    }
                }
                
                
            }catch {
                DebugLog(database.lastErrorMessage())
            }
            
            database.close()
        }
    }
    
    //MARK: - 新增panel或inverter 数据
    func insertSerialNumberData(model: PanelNumberImageDBModel,tableName: String) {
        if openDatabase() {
            var sqlStr: String = ""
            do{
                sqlStr = "insert into \(tableName)(\(field_pvdId),\(panelnumber_field_original_code),\(panelnumber_field_code),\(field_recordAt),\(field_uploadingFlag),\(panelnumber_field_image),\(panelnumber_field_type),\(panelnumber_field_auto_scan),\(panelnumber_field_latitude),\(panelnumber_field_longitude),\(panelnumber_field_barcodetype),\(panelnumber_field_scan_type),\(panelnumber_field_scan_confidence),\(panelnumber_field_scan_timespan))  values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
                try database.executeUpdate(sqlStr, values: [model.pvd_id,(model.original_code),model.code,model.record_at,"0",model.image,DataOperType.Add.rawValue,model.auto_scan,model.latitude as Any,model.longitude as Any, model.barcode_type,model.scan_type, model.confidence,model.scan_timespan])
            }catch {
                
                DebugLog(database.lastErrorMessage())
            }
            database.close()
        }
    }
    //MARK: - 删除已经上传的panel，或者 数据
    func delSerialNumberData(model: PanelNumberImageDBModel,tableName: String){
        if openDatabase() {
            
            do{
                if model.dbId != 0 {
                    let sqlStr: String = "update \(tableName) set \(panelnumber_field_type) = ? where \(field_keyId) = ?"
                    try database.executeUpdate(sqlStr, values: [DataOperType.Del.rawValue, model.dbId])
                }else{
                    let sqlStr: String = "insert into \(tableName)(\(field_pvdId),\(panelnumber_field_imageId),\(panelnumber_field_code),\(panelnumber_field_image),\(field_uploadingFlag),\(panelnumber_field_type)) values(?,?,?,?,?,?)"
                    try database.executeUpdate(sqlStr, values: [model.pvd_id,model.id,model.code,model.image  ,"0",DataOperType.Del.rawValue])
                }
            }catch {
                
                DebugLog(database.lastErrorMessage())
            }
            database.close()
        }
    }
    //MARK: - 修改已经上传的panel或inverter 数据
    func modifySerialNumberData(model: PanelNumberImageDBModel,tableName: String){
        if openDatabase() {
            var sqlStr: String = ""
            do{
                //1.检查是否存在本地数据
                let checkSQL = "select count(*) from \(tableName) where \(field_pvdId) = ? and \(panelnumber_field_code) = ? "
                
                let results: FMResultSet = try database.executeQuery(checkSQL, values: [model.pvd_id,model.original_code])
                if results.next(){
                    let cnt: Int32 = results.int(forColumnIndex: 0)
                    if cnt > 0 {//修改已经有的本地数据
                        sqlStr = "update \(tableName) set \(panelnumber_field_code) = ? where \(field_pvdId) = ? and \(panelnumber_field_code) = ? "
                        try database.executeUpdate(sqlStr, values: [model.code,model.pvd_id,model.original_code])
                        
                    }else{//modify server panel number
                        sqlStr = "insert into \(tableName)(\(field_pvdId),\(panelnumber_field_imageId),\(panelnumber_field_code),\(panelnumber_field_image),\(panelnumber_field_latitude),\(panelnumber_field_longitude),\(panelnumber_field_type),\(panelnumber_field_auto_scan),\(panelnumber_field_scan_type),\(panelnumber_field_barcodetype),\(field_uploadingFlag)) values(?,?,?,?,?,?,?,?,?,?,?)"
                        
                        try database.executeUpdate(sqlStr, values: [model.pvd_id,model.id,model.code,model.image,model.latitude as Any,model.longitude as Any,DataOperType.Modify.rawValue,model.auto_scan,model.scan_type,model.barcode_type,"0"])
                    }
                }
            }catch {
                
                DebugLog(database.lastErrorMessage())
            }
            database.close()
        }
    }
    //MARK: - 获取当前job的所有checklist本地数据 text,bool,images
    func loadAllChecklistData(pvdId: String) -> [PVDChecklistModel] {
        var models: [PVDChecklistModel] = [PVDChecklistModel]()
        
        if openDatabase() {
            do {
                //去掉and \(field_uploadingFlag) = '0'，防止checklist image list上上传慢的时候会漏显示本地数据的问题
                let query = "select * from \(checklist_table_name) where \(field_pvdId) = \(pvdId) "
                
                let results = try database.executeQuery(query, values: nil)
                
                while results.next() {
                    let model = PVDChecklistModel()
                    let checklistId: Int32 = results.int(forColumn: "\(checklist_field_checklistId)")
                    let title = results.string(forColumn: "\(checklist_field_title)") ?? ""
                    model.dbId = results.int(forColumn: "\(field_keyId)")
                    model.id = checklistId
                    model.type = results.string(forColumn: "\(checklist_field_type)") ?? ""
                    model.title = title
                    model.answer = results.string(forColumn: "\(checklist_field_answer)") ?? ""
                    model.answered_at = results.string(forColumn: "\(checklist_field_answered_at)") ?? ""
                    model.full_name = results.string(forColumn: "\(checklist_field_full_name)") ?? ""
                    model.pvdId = pvdId
                    let subQuery: String = "select * from \(checklistImage_table_name) where \(field_pvdId) = \(pvdId) and \(checklist_field_checklistId) = \(checklistId)  order by \(field_recordAt) asc"
                    
                    let imageResults = try database.executeQuery(subQuery, values: nil)
                    
                    while imageResults.next() {
                        let imageModel = CheckListImageDBModel()
                        imageModel.dbId = imageResults.int(forColumn: "\(field_keyId)")
                        imageModel.pvd_id = pvdId
                        imageModel.checklist_id = checklistId
                        imageModel.title = title
                        imageModel.uploading_flag = imageResults.string(forColumn: "\(field_uploadingFlag)") ?? ""
                        imageModel.id = imageResults.string(forColumn: "\(checklistImage_field_imageId)") ?? ""
                        imageModel.record_at = imageResults.string(forColumn: "\(field_recordAt)") ?? ""
                        imageModel.photo_source = imageResults.string(forColumn: "\(checklistImage_field_photo_source)") ?? ""
                        imageModel.photo_created_at = imageResults.string(forColumn: "\(checklistImage_field_photo_created_at)") ?? ""
                        imageModel.longitude = imageResults.string(forColumn: "\(checklistImage_field_photo_longitude)") ?? ""
                        imageModel.latitude = imageResults.string(forColumn: "\(checklistImage_field_photo_latitude)") ?? ""
                        
                        let oldPath = imageResults.string(forColumn: "\(checklistImage_field_image)")
                        var localFlag: Bool = false
                        
                        if oldPath != nil && oldPath != ""{
                            localFlag = oldPath!.contains("/GreenDeal/")
                            
                        }
                        if localFlag  {//local add images
                            imageModel.image = FileUtil().getRealFilePath(oldPath!)
                            imageModel.oper_type = DataOperType.Add.rawValue
                            model.localAddImages.append(imageModel)
                            
                        }else{//delete image from server
                            imageModel.image = (oldPath ?? "")
                            imageModel.oper_type = DataOperType.Del.rawValue
                            model.localDeletedImages.append(imageModel)
                        }
                    }
                    models.append(model)
                }
                
            }
            catch {
                DebugLog(database.lastErrorMessage())
            }
            database.close()
        }
        
        return models
    }
    //MARK: - 获取当前pvd的panel 或inverter  本地数据，包括增加，修改，删除的
    func loadLocalSeriallNumber(pvdId: String,tableName: String) -> [PanelNumberImageDBModel] {
        var models: [PanelNumberImageDBModel] = [PanelNumberImageDBModel]()
        
        if openDatabase() {
            do {
                let query = "select * from \(tableName) where \(field_pvdId) = ?  order by \(field_recordAt) asc"
                
                let results = try database.executeQuery(query, values: [pvdId,DataOperType.Add.rawValue])
                
                while results.next() {
                    let model = PanelNumberImageDBModel()
                    model.id = results.string(forColumn: "\(panelnumber_field_imageId)") ?? ""
                    model.dbId = results.int(forColumn: "\(field_keyId)")
                    model.pvd_id = results.string(forColumn: "\(field_pvdId)") ?? ""
                    model.original_code = results.string(forColumn: "\(panelnumber_field_original_code)") ?? ""
                    model.code = results.string(forColumn: "\(panelnumber_field_code)") ?? ""
                    let latitude = results.double(forColumn: "\(panelnumber_field_latitude)")
                    let longitude = results.double(forColumn: "\(panelnumber_field_longitude)")
                    if latitude == 0 && longitude == 0 {
                        model.latitude = nil
                        model.longitude = nil
                    }else{
                        model.latitude = latitude
                        model.longitude = longitude
                    }
                    
                    model.barcode_type = results.string(forColumn: "\(panelnumber_field_barcodetype)") ?? ""
                    if let imagePath = results.string(forColumn: "\(panelnumber_field_image)") {
                        let realPath = FileUtil().getRealFilePath(imagePath)
                        model.image = realPath
                    }
                    
                    //是扫描，还是手工增加
                    if model.image != "" {
                        let dic = Bundle.main.infoDictionary
                        
                        if let currentAppVersion = dic!["CFBundleShortVersionString"] as? String , currentAppVersion < "3.0.0" {
                            model.auto_scan = 1
                            
                        }else{
                            model.auto_scan = results.int(forColumn: "\(panelnumber_field_auto_scan)")
                        }
                        
                        
                    }else{
                        model.auto_scan = 0
                    }
                    model.scan_type = results.int(forColumn: "\(panelnumber_field_scan_type)")
                    model.record_at = results.string(forColumn: "\(field_recordAt)") ?? ""
                    model.uploading_flag = results.string(forColumn: "\(field_uploadingFlag)") ?? ""
                    model.operType = (results.string(forColumn: "\(panelnumber_field_type)") ?? "")
                    model.confidence = results.int(forColumn: "\(panelnumber_field_scan_confidence)")
                    model.scan_timespan = results.int(forColumn: "\(panelnumber_field_scan_timespan)")
                    models.append(model)
                }
                
            }
            catch {
                DebugLog(database.lastErrorMessage())
            }
            database.close()
        }
        
        return models
    }
    //MARK: - 获取签名的本地数据(installer,designer,owner,witness)
    func loadAllSignatueData(_ pvdId: String) -> [SignatureImageDBModel] {
        var models = [SignatureImageDBModel]()
        if openDatabase() {
            do {
                
                let query = "select * from \(signature_table_name) where \(field_pvdId) = \(pvdId) and \(field_uploadingFlag) = '0' "
                
                let results = try database.executeQuery(query, values: nil)
                
                while results.next() {
                    let model = SignatureImageDBModel()
                    
                    model.dbId = results.int(forColumn: "\(field_keyId)")
                    model.pvd_id = results.string(forColumn: "\(field_pvdId)") ?? ""
                    model.print_name = results.string(forColumn: "\(signature_field_print_name)") ?? ""
                    model.image_data = results.data(forColumn: "\(field_imageData)") as NSData?
                    model.signed_at = results.string(forColumn: "\(signature_field_signed_at)") ?? ""
                    model.uploading_flag = results.string(forColumn: "\(field_uploadingFlag)") ?? ""
                    if let rawValue = results.string(forColumn: "\(signature_field_type)"), let type = SignatureType(rawValue: rawValue){
                        model.signature_type = type
                    }
                    //
                    let latitude = results.double(forColumn: "\(signature_field_latitude)")
                    let longitude = results.double(forColumn: "\(signature_field_longitude)")
                    if latitude == 0 && longitude == 0 {
                        model.installer_signature_latitude = nil
                        model.installer_signature_longitude = nil
                    }else{
                        model.installer_signature_latitude = latitude
                        model.installer_signature_longitude = longitude
                    }
                    model.installer_agree_selfie = results.data(forColumn: "\(signature_field_installer_agree_selfie)") as NSData?
                    model.installer_signature_selfie = results.data(forColumn: "\(signature_field_installer_signature_selfie)") as NSData?
                    model.installer_statement_duty_type = results.string(forColumn: "\(signature_field_installer_duty_type)") ?? ""
                    model.installer_statement_design_type = results.string(forColumn: "\(signature_field_installer_design_type)") ?? ""
                    
                    model.owner_signature_selfie = results.data(forColumn: "\(signature_field_owner_signature_selfie)") as NSData?
                    model.physically_attended = results.string(forColumn: "\(signature_field_owner_physically_attended)") ?? ""
                    model.owner_aussolarco_satisfactory_version = results.string(forColumn: "\(signature_field_owner_aussolarco_satisfactory_version)") ?? ""
                    model.owner_satisfaction_completion_version = results.string(forColumn: "\(signature_field_owner_aussolarco_completion_version)") ?? ""
                    model.owner_mandatory_declaration_version = results.string(forColumn: "\(signature_field_owner_mandatory_declaration_version)") ?? ""
                    model.owner_financial_incentive = results.string(forColumn: "\(signature_field_owner_financial_incentive)") ?? ""
                    model.statement_input_param_all = results.string(forColumn: "\(signature_field_statement_input_param_all)") ?? ""

                    models.append(model)
                }
                
            }
            catch {
                DebugLog(database.lastErrorMessage())
            }
            database.close()
        }
        
        return models
    }
    //MARK: - 增加本地签名数据
    func insertSignatureData(model: SignatureImageDBModel) {
        if openDatabase() {
            do{
                //1.check if checklist data exist in master table
                let checkSQL = "select count(*) from \(signature_table_name) where \(field_pvdId) = \(model.pvd_id) and \(signature_field_type) = ? "
                
                let results: FMResultSet = try database.executeQuery(checkSQL, values: [model.signature_type.rawValue])
                if results.next(){
                    let cnt: Int32 = results.int(forColumnIndex: 0)
                    if cnt > 0 {
                        let sqlStr = "update \(signature_table_name) set \(signature_field_print_name) = ?,\(field_imageData) = ?,\(signature_field_signed_at) = ?,\(signature_field_latitude) = ?,\(signature_field_longitude) = ?,\(signature_field_installer_agree_selfie) = ?,\(signature_field_installer_signature_selfie) = ?,\(signature_field_owner_signature_selfie) = ?,\(signature_field_owner_physically_attended) = ?,\(signature_field_owner_aussolarco_satisfactory_version) = ?,\(signature_field_owner_aussolarco_completion_version) = ?,\(signature_field_owner_mandatory_declaration_version) = ?,\(signature_field_installer_duty_type) = ?,\(signature_field_installer_design_type) = ?,\(signature_field_owner_financial_incentive) = ?,\(signature_field_statement_input_param_all) = ?  where \(field_pvdId) = \(model.pvd_id) and \(signature_field_type) = ?"
                        
                        
                        try database.executeUpdate(sqlStr, values: [model.print_name ,model.image_data as Any,model.signed_at,model.installer_signature_latitude as Any, model.installer_signature_longitude as Any, model.installer_agree_selfie as Any ,model.installer_signature_selfie as Any, model.owner_signature_selfie as Any,model.physically_attended,model.owner_aussolarco_satisfactory_version ,model.owner_satisfaction_completion_version ,model.owner_mandatory_declaration_version ,model.installer_statement_duty_type ,model.installer_statement_design_type ,model.owner_financial_incentive ,model.statement_input_param_all ,model.signature_type.rawValue])
                    }else{
                        let sqlStr = "insert into \(signature_table_name)(\(field_pvdId),\(signature_field_type),\(signature_field_print_name),\(field_imageData),\(signature_field_signed_at),\(field_uploadingFlag),\(signature_field_latitude),\(signature_field_longitude),\(signature_field_installer_agree_selfie),\(signature_field_installer_signature_selfie),\(signature_field_owner_signature_selfie),\(signature_field_owner_physically_attended),\(signature_field_owner_aussolarco_satisfactory_version),\(signature_field_owner_aussolarco_completion_version),\(signature_field_owner_mandatory_declaration_version),\(signature_field_installer_duty_type),\(signature_field_installer_design_type),\(signature_field_owner_financial_incentive),\(signature_field_statement_input_param_all)) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
                        
                        
                        try database.executeUpdate(sqlStr, values: [model.pvd_id,model.signature_type.rawValue,model.print_name ,model.image_data as Any,model.signed_at,"0",model.installer_signature_latitude as Any, model.installer_signature_longitude as Any, model.installer_agree_selfie as Any ,model.installer_signature_selfie as Any, model.owner_signature_selfie as Any,model.physically_attended,model.owner_aussolarco_satisfactory_version,model.owner_satisfaction_completion_version ,model.owner_mandatory_declaration_version,model.installer_statement_duty_type ,model.installer_statement_design_type ,model.owner_financial_incentive ,model.statement_input_param_all])
                    }
                }
                
                
            }catch {
                DebugLog(database.lastErrorMessage())
            }
            database.close()
        }
    }
    //MARK: - 根据签名类型删除本地数据
    func deleteSignatueDataBySignType(_ pvdId: String, signatueType: SignatureType) {
        
        if openDatabase() {
            do {
                
                let delSql = "delete from \(signature_table_name) where \(field_pvdId) = \(pvdId) and \(signature_field_type) = ? "
                try database.executeUpdate(delSql, values: [signatueType.rawValue])
            }
            catch {
                DebugLog(database.lastErrorMessage())
            }
            database.close()
        }
    }
    //MARK: - 获取当前job的所有checklist image,text,bool本地数据的数量
    func getLocalChecklistDataCount(pvdId: String) -> Int32 {
        var localCount: Int32 = 0
        if openDatabase() {
            do {
                //1.获取text，bool的数量
                let query = "select count(*) from \(checklist_table_name) where \(field_pvdId) = ? and \(checklist_field_type) in (?,?) "
                let results = try database.executeQuery(query, values: [pvdId, ChecklistType.Text.rawValue, ChecklistType.Bool.rawValue])
                while results.next() {
                    let cnt: Int32 = results.int(forColumnIndex: 0)
                    localCount = cnt
                }
                //2.获取image的数量
                let subQuery: String = "select count(*) from \(checklistImage_table_name) where \(field_pvdId) = ?"
                let imageResults = try database.executeQuery(subQuery, values: [pvdId])
                while imageResults.next() {
                    let cnt: Int32 = imageResults.int(forColumnIndex: 0)
                    localCount += cnt
                }
            }
            catch {
                DebugLog(database.lastErrorMessage())
            }
            database.close()
        }
        return localCount
    }
    //MARK: - 获取当前service job的所有checklist image,text,bool本地数据的数量
    func getServiceLocalDataCount(serviceId: String) -> Int32 {
        var localCount: Int32 = 0
        if openDatabase() {
            do {
                //1.获取text，bool的数量
                let query = "select count(*) from \(service_checklist_table_name) where \(field_serviceId) = ? and \(checklist_field_type) in (?,?) "
                let results = try database.executeQuery(query, values: [serviceId, ChecklistType.Text.rawValue, ChecklistType.Bool.rawValue])
                while results.next() {
                    let cnt: Int32 = results.int(forColumnIndex: 0)
                    localCount = cnt
                }
                //2.获取image的数量
                let subQuery: String = "select count(*) from \(service_checklistImage_table_name) where \(field_serviceId) = ?"
                let imageResults = try database.executeQuery(subQuery, values: [serviceId])
                while imageResults.next() {
                    let cnt: Int32 = imageResults.int(forColumnIndex: 0)
                    localCount += cnt
                }
            }
            catch {
                DebugLog(database.lastErrorMessage())
            }
            database.close()
        }
        return localCount
    }
    //MARK: - 删除text， bool的本地数据(pvd job, service job共用)
    func deleteChecklsitData(pvdId:String,checklistId: Int32, jobType: JobType = .pvdJob){
        if openDatabase() {
            var delSql = " "
            if jobType == .pvdJob {//pvd job
                delSql = "delete from \(checklist_table_name) where \(field_pvdId) = ? and \(checklist_field_checklistId) = ? "
            }else {
                delSql = "delete from \(service_checklist_table_name) where \(field_serviceId) = ? and \(checklist_field_checklistId) = ? "
            }
            do {
                try database.executeUpdate(delSql, values: [pvdId,checklistId])
                
            }
            catch {
                DebugLog(database.lastErrorMessage())
            }
            
            
            database.close()
        }
    }
    //MARK: - 删除checklist image data
    func deleteServiceChecklistImageData(model: CheckListImageDBModel){
        
        if openDatabase() {
            //1.delete image
            let delSql = "delete from \(service_checklistImage_table_name) where \(field_serviceId) = ? and \(checklist_field_checklistId) = ? and \(checklistImage_field_imageId) = ? "
            do {
                try database.executeUpdate(delSql, values: [model.pvd_id,model.checklist_id,model.id])
                
            }
            catch {
                DebugLog(error.localizedDescription)
            }
            //2.check if image of current checklist exist ,if not , delete checklist data in master table
            let checkSQL = "select count(*) from \(service_checklistImage_table_name) where \(field_serviceId) = \(model.pvd_id) and \(checklist_field_checklistId) = \(model.checklist_id)"
            do {
                
                let results: FMResultSet = try database.executeQuery(checkSQL, values: nil)
                if results.next(){
                    let cnt: Int32 = results.int(forColumnIndex: 0)
                    if cnt == 0 {
                        let delMasterSql = "delete from \(service_checklist_table_name) where \(field_serviceId) = \(model.pvd_id) and \(checklist_field_checklistId) = \(model.checklist_id)"
                        
                        try database.executeUpdate(delMasterSql, values: nil)
                    }
                }
                
            }
            catch {
                DebugLog(database.lastErrorMessage())
            }
            database.close()
        }
    }
    //MARK: - 删除checklist image data
    func deleteChecklistImageData(model: CheckListImageDBModel){
        
        if openDatabase() {
            //1.delete image
            let delSql = "delete from \(checklistImage_table_name) where \(field_pvdId) = ? and \(checklist_field_checklistId) = ? and \(checklistImage_field_imageId) = ? "
            
            do {
                try database.executeUpdate(delSql, values: [model.pvd_id,model.checklist_id,model.id])
                
            }
            catch {
                DebugLog(error.localizedDescription)
            }
            //2.check if image of current checklist exist ,if not , delete checklist data in master table
            let checkSQL = "select count(*) from \(checklistImage_table_name) where \(field_pvdId) = \(model.pvd_id) and \(checklist_field_checklistId) = \(model.checklist_id)"
            do {
                
                let results: FMResultSet = try database.executeQuery(checkSQL, values: nil)
                if results.next(){
                    let cnt: Int32 = results.int(forColumnIndex: 0)
                    if cnt == 0 {
                        let delMasterSql = "delete from \(checklist_table_name) where \(field_pvdId) = \(model.pvd_id) and \(checklist_field_checklistId) = \(model.checklist_id)"
                        
                        try database.executeUpdate(delMasterSql, values: nil)
                    }
                }
                
            }
            catch {
                DebugLog(database.lastErrorMessage())
            }
            database.close()
        }
    }
    //MARK: - 根据barcode删除panel或inverter的数据
    func deleteLocalBarcodeByCode(tableName: String,code: String){
        //delete
        let delSql = "delete from \(tableName) where \(panelnumber_field_code) = ? "
        if openDatabase() {
            do {
                try database.executeUpdate(delSql, values: [code])
            }
            catch {
                DebugLog(database.lastErrorMessage())
            }
            database.close()
        }
    }
    //MARK: - 根据db id，删除本地数据
    func deleteLocalSignatureByDBId(tableName: String,keyId: Int32){
        //delete
        let delSql = "delete from \(tableName) where \(field_keyId) = \(keyId) "
        if openDatabase() {
            do {
                
                try database.executeUpdate(delSql, values:nil)
                
            }
            catch {
                DebugLog(database.lastErrorMessage())
            }
            database.close()
        }
    }
    //MARK: - 根据db id，删除本地数据
    func deleteLocalPanelInverterByCode(tableName: String, code: String){
        //delete
        let delSql = "delete from \(tableName) where \(panelnumber_field_code) = \(code) "
        if openDatabase() {
            
            do {
                try database.executeUpdate(delSql, values:nil)
                
            }
            catch {
                DebugLog(database.lastErrorMessage())
            }
            database.close()
        }
    }
    //MARK: - 根据barcode检查panel或inverter的是否在上传中
    func checkLocalBarcodeUploadingByCode(tableName: String,code: String)->Bool{
        var uploadFlag: Bool = false
        //delete
        let query = "select * from \(tableName) where \(panelnumber_field_code) = ? and \(field_uploadingFlag) = '1'"
        if openDatabase() {
            do {
                let results = try database.executeQuery(query, values:  [code])
                while results.next() {
                    uploadFlag = true
                }
            }
            catch {
                DebugLog(database.lastErrorMessage())
            }
            database.close()
        }
        return uploadFlag
    }
    //MARK: - 设置上传标记
    func setUpoadingFlag(_ tableName: String,keyId: Int32, flag: String){
        if openDatabase() {
            let delSql = "update \(tableName) set \(field_uploadingFlag) = ?  where \(field_keyId) = ? "
            do {
                try database.executeUpdate(delSql, values: [flag,keyId])
            }
            catch {
                DebugLog(database.lastErrorMessage())
            }
            
            database.close()
        }
    }
    //MARK: - 设置panel 或inverter的上传标记，参数有key id替换为code
    func setUpoadingFlagForBarcode(_ tableName: String,code: String, flag: String){
        if openDatabase() {
            let delSql = "update \(tableName) set \(field_uploadingFlag) = ?  where \(panelnumber_field_code) = ? "
            do {
                try database.executeUpdate(delSql, values: [flag,code])
            }
            catch {
                DebugLog(database.lastErrorMessage())
            }
            
            database.close()
        }
    }
    //MARK: - 每次启动APP时，将没有上传成功但是upload_flag = "1"(正在上传标记)的数据全部重置为0，防止丢失数据
    func resetUploadFlag(){
        if openDatabase() {
            
            do {
                //1.checklist master table
                var updateSql: String = "update \(checklist_table_name) set \(field_uploadingFlag) = '0' where  \(field_uploadingFlag) = '1' "
                
                try database.executeUpdate(updateSql, values:nil)
                //1.1 checklist image
                updateSql = "update \(checklistImage_table_name) set \(field_uploadingFlag) = '0' where  \(field_uploadingFlag) = '1' "
                
                try database.executeUpdate(updateSql, values:nil)
                //2.panel number image
                updateSql = "update \(panelnumber_table_name) set \(field_uploadingFlag) = '0' where  \(field_uploadingFlag) = '1' "
                
                try database.executeUpdate(updateSql, values:nil)
                //2.1 inverter number image
                updateSql = "update \(inverternumber_table_name) set \(field_uploadingFlag) = '0' where  \(field_uploadingFlag) = '1' "
                
                try database.executeUpdate(updateSql, values:nil)
                //3.signatures image
                updateSql = "update \(signature_table_name) set \(field_uploadingFlag) = '0' where  \(field_uploadingFlag) = '1' "
                try database.executeUpdate(updateSql, values:nil)
            }
            catch {
                DebugLog(database.lastErrorMessage())
            }
            
            database.close()
        }
    }
    func resetServiceUploadFlag(){
        if openDatabase() {
            
            do {
                
                //1.service checklist
                var updateSql = "update \(service_checklist_table_name) set \(field_uploadingFlag) = '0' where  \(field_uploadingFlag) = '1' "
                
                try database.executeUpdate(updateSql, values:nil)
                //1.1 service checklist image
                updateSql = "update \(service_checklistImage_table_name) set \(field_uploadingFlag) = '0' where  \(field_uploadingFlag) = '1' "
                
                try database.executeUpdate(updateSql, values:nil)
            }
            catch {
                DebugLog(database.lastErrorMessage())
            }
            
            database.close()
        }
    }
    //只有service job暂时用
    func setUpoadingFlag(_ tableName: String,checklistId: Int32, imageId: String, flag: String){
        if openDatabase() {
            let delSql = "update \(tableName) set \(field_uploadingFlag) = ?  where \(checklist_field_checklistId) = ? and \(checklistImage_field_imageId) = ?  "
            do {
                try database.executeUpdate(delSql, values: [flag,checklistId,imageId])
            }
            catch {
                DebugLog(database.lastErrorMessage())
            }
            
            database.close()
        }
    }
    
    //MARK: - 插入service checklist image数据
    func insertServiceChecklistImages(model: CheckListImageDBModel) {
        if openDatabase() {
            database.beginTransaction()
            do{
                
                //2.add image data
                let imageStr = "insert into \(service_checklistImage_table_name) (\(field_serviceId),\(checklist_field_checklistId),\(checklistImage_field_imageId),\(checklistImage_field_image),\(field_recordAt),\(field_uploadingFlag),\(checklist_field_title)) values('\(model.pvd_id)',\(model.checklist_id),'\(model.id)','\(model.image)','\(model.record_at)','0','\(model.title)')"
                
                let result = database.executeStatements(imageStr)
                
                if result {
                    //                    print("********增加图片成功")
                    //1.check if checklist data exist in master table
                    let checkSQL = "select count(*) from \(service_checklist_table_name) where \(field_serviceId) = \(model.pvd_id) and \(checklist_field_checklistId) = \(model.checklist_id)"
                    
                    let results: FMResultSet = try database.executeQuery(checkSQL, values: nil)
                    if results.next(){
                        let cnt: Int32 = results.int(forColumnIndex: 0)
                        if cnt == 0 {
                            let addMasterSql = "insert into \(service_checklist_table_name) (\(field_serviceId),\(checklist_field_checklistId),\(checklist_field_type),\(checklist_field_title),\(checklist_field_full_name),\(checklist_field_answered_at)) values('\(model.pvd_id)',\(model.checklist_id),'image','\(model.title  )','\(APPConfig.loginInfo.fullName)','\(model.record_at)')"
                            
                            let masterResult = database.executeStatements(addMasterSql)
                            if masterResult {
                                //                                print("********增加checklist 主表成功")
                                database.commit()
                            }else{
                                //                                print("********增加checklist 主表失败")
                                DebugLog(database.lastErrorMessage())
                                database.rollback()
                            }
                        }else{
                            database.commit()
                        }
                    }
                }else{
                    //                    print("********增加图片失败")
                    DebugLog(database.lastErrorMessage())
                    database.rollback()
                }
                
            }catch {
                DebugLog(database.lastErrorMessage())
            }
            
            database.close()
        }
    }
    //MARK: insert checklist data
    func insertServiceChecklistData(model: PVDChecklistModel) {
        if openDatabase() {
            
            do{
                let checkSQL = "select count(*) from \(service_checklist_table_name) where \(field_serviceId) = \(model.pvdId) and \(checklist_field_checklistId) = \(model.id)"
                
                let sqlStr = "insert into \(service_checklist_table_name) (\(field_serviceId),\(checklist_field_checklistId),\(checklist_field_type),\(checklist_field_title),\(checklist_field_answer),\(checklist_field_answered_at),\(checklist_field_full_name)) values(?,?,?,?,?,?,?)"
                
                let results: FMResultSet = try database.executeQuery(checkSQL, values: nil)
                if results.next(){
                    let cnt: Int32 = results.int(forColumnIndex: 0)
                    if cnt == 0 {
                        try database.executeUpdate(sqlStr, values: [model.pvdId,model.id,model.type, model.title ,model.answer ,model.answered_at,model.full_name])
                    }else{
                        let updateSql = "update \(service_checklist_table_name) set \(checklist_field_answer) = ?,\(checklist_field_answered_at) = ?,\(checklist_field_title) = ?,\(checklist_field_full_name) = ? where \(field_serviceId) = ? and \(checklist_field_checklistId) = ?"
                        
                        try database.executeUpdate(updateSql, values: [model.answer,model.answered_at ,model.title ,model.full_name ,model.pvdId,model.id])
                    }
                    
                }
                
            }catch {
                DebugLog(database.lastErrorMessage())
            }
            
            database.close()
        }
    }
    
    //MARK: - 加载所有Service checklist 本地数据
    func loadAllServiceChecklistData(serviceId: String) -> [PVDChecklistModel] {
        var models: [PVDChecklistModel] = [PVDChecklistModel]()
        
        if openDatabase() {
            do {
                let query = "select * from \(service_checklist_table_name) where \(field_serviceId) = \(serviceId) and \(field_uploadingFlag) = '0'"
                
                let results = try database.executeQuery(query, values: nil)
                
                while results.next() {
                    let model = PVDChecklistModel()
                    let checklistId: Int32 = results.int(forColumn: "\(checklist_field_checklistId)")
                    let title = results.string(forColumn: "\(checklist_field_title)") ?? ""
                    model.dbId = results.int(forColumn: "\(field_keyId)")
                    model.id = checklistId
                    model.pvdId = "\(serviceId)"
                    model.type = results.string(forColumn: "\(checklist_field_type)") ?? ""
                    model.title = title
                    model.answer = results.string(forColumn: "\(checklist_field_answer)") ?? ""
                    model.answered_at = results.string(forColumn: "\(checklist_field_answered_at)") ?? ""
                    model.full_name = results.string(forColumn: "\(checklist_field_full_name)") ?? ""
                    
                    let subQuery: String = "select * from \(service_checklistImage_table_name) where \(field_serviceId) = \(serviceId) and \(checklist_field_checklistId) = \(checklistId)  and \(field_uploadingFlag) = '0' order by \(field_recordAt) asc"
                    
                    let imageResults = try database.executeQuery(subQuery, values: nil)
                    
                    while imageResults.next() {
                        let imageModel = CheckListImageDBModel()
                        imageModel.dbId = imageResults.int(forColumn: "\(field_keyId)")
                        imageModel.pvd_id = serviceId
                        imageModel.checklist_id = checklistId
                        imageModel.title = title
                        imageModel.uploading_flag = imageResults.string(forColumn: "\(field_uploadingFlag)") ?? ""
                        imageModel.id = imageResults.string(forColumn: "\(checklistImage_field_imageId)") ?? ""
                        imageModel.record_at = imageResults.string(forColumn: "\(field_recordAt)") ?? ""
                        
                        let oldPath = imageResults.string(forColumn: "\(checklistImage_field_image)") ?? ""
                        var localFlag: Bool = false
                        
                        if oldPath != ""{
                            localFlag = oldPath.contains("/GreenDeal/")
                            
                        }
                        if localFlag  {//local add images
                            imageModel.image = FileUtil().getRealFilePath(oldPath)
                            imageModel.oper_type = DataOperType.Add.rawValue
                            model.localAddImages.append(imageModel)
                            
                        }else{//delete image from server
                            imageModel.image = oldPath
                            imageModel.oper_type = DataOperType.Del.rawValue
                            model.localDeletedImages.append(imageModel)
                        }
                    }
                    models.append(model)
                }
                
            }
            catch {
                DebugLog(database.lastErrorMessage())
            }
            database.close()
        }
        
        return models
    }
    //load service checklist , only for reupload
    func loadAllServiceChecklistForReupload(_ serviceId: Int32)->[PVDChecklistModelForReupload]{
        var models: [PVDChecklistModelForReupload] = [PVDChecklistModelForReupload]()
        
        if openDatabase() {
            do {
                let query = "select * from \(service_checklist_table_name) where \(field_serviceId) = \(serviceId) and \(field_uploadingFlag) = '0'  order by \(field_serviceId),\(checklist_field_checklistId) "
                
                let results = try database.executeQuery(query, values: nil)
                
                while results.next() {
                    if let checklistType = results.string(forColumn: "\(checklist_field_type)") {
                        let checklistId: Int32 = results.int(forColumn: "\(checklist_field_checklistId)")
                        if checklistType == "image" {
                            
                            let subQuery: String = "select * from \(service_checklistImage_table_name) where \(field_serviceId) = \(serviceId) and \(checklist_field_checklistId) = \(checklistId)  and \(field_uploadingFlag) = '0'"
                            
                            let imageResults = try database.executeQuery(subQuery, values: nil)
                            
                            while imageResults.next() {
                                let model = PVDChecklistModelForReupload()
                                model.dbId = results.int(forColumn: "\(field_keyId)")
                                model.serviceId = serviceId
                                model.checklistId = checklistId
                                model.type = checklistType
                                model.title = imageResults.string(forColumn: "\(checklist_field_title)") ?? ""
                                model.answer = results.string(forColumn: "\(checklist_field_answer)") ?? ""
                                model.answered_at = results.string(forColumn: "\(checklist_field_answered_at)") ?? ""
                                model.imageDBId = imageResults.int(forColumn: "\(field_keyId)")
                                model.image_record_at = imageResults.string(forColumn: "\(field_recordAt)") ?? ""
                                model.id = imageResults.string(forColumn: "\(checklistImage_field_imageId)") ?? ""
                                
                                let imagePath = imageResults.string(forColumn: "\(checklistImage_field_image)") ?? ""
                                var localFlag: Bool = false
                                
                                if imagePath != ""{
                                    localFlag = imagePath.contains("/GreenDeal/")
                                    
                                }
                                if localFlag  {//local add images
                                    let realPath = FileUtil().getRealFilePath(imagePath)
                                    model.image = realPath
                                    model.operType = ChecklistImageOperType.Add.rawValue
                                }else{//delete image from server
                                    model.image = imageResults.string(forColumn: "\(checklistImage_field_image)") ?? ""
                                    model.operType = ChecklistImageOperType.Del.rawValue
                                }
                                models.append(model)
                            }
                            
                        }else{//bool,text
                            let model = PVDChecklistModelForReupload()
                            model.dbId = results.int(forColumn: "\(field_keyId)")
                            model.serviceId = serviceId
                            model.checklistId = checklistId
                            model.type = checklistType
                            model.title = results.string(forColumn: "\(checklist_field_title)") ?? ""
                            model.answer = results.string(forColumn: "\(checklist_field_answer)") ?? ""
                            model.answered_at = results.string(forColumn: "\(checklist_field_answered_at)") ?? ""
                            models.append(model)
                        }
                    }
                    
                    
                    
                }
                
            }
            catch {
                DebugLog(database.lastErrorMessage())
            }
            database.close()
        }
        
        return models
    }
}

